1 Imports System.Data.SqlClient
2 Public Class frmClass
3 Sub fillCombo()
4 Try
5 con = New SqlConnection(cs)
6 con.Open()
7 adp = New SqlDataAdapter()
8 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(Type) FROM ClassType", con)
9 ds = New DataSet("ds")
10 adp.Fill(ds)
11 dtable = ds.Tables(0)
12 cmbClassType.Items.Clear()
13 For Each drow As DataRow In dtable.Rows
14 cmbClassType.Items.Add(drow(0).ToString())
15 Next
16 Catch ex As Exception
17 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
18 End Try
19 End Sub
20 Sub Reset()
21 cmbClassType.SelectedIndex = -1
22 txtSearchByClass.Text = ""
23 txtSearchByClassType.Text = ""
24 txtClass.Text = ""
25 txtClass.Focus()
26 btnSave.Enabled = True
27 btnUpdate.Enabled = False
28 btnDelete.Enabled = False
29 Getdata()
30 End Sub
31 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
32 Me.Close()
33 End Sub
34
35 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
36 If Len(Trim(txtClass.Text)) = 0 Then
37 MessageBox.Show("Please enter class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
38 txtClass.Focus()
39 Exit Sub
40 End If
41 If Len(Trim(cmbClassType.Text)) = 0 Then
42 MessageBox.Show("Please select class type", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
43 cmbClassType.Focus()
44 Exit Sub
45 End If
46 Try
47 con = New SqlConnection(cs)
48 con.Open()
49 Dim ct As String = "select ClassName from Class where ClassName=@d1"
50 cmd = New SqlCommand(ct)
51 cmd.Connection = con
52 cmd.Parameters.AddWithValue("@d1", txtClass.Text)
53 rdr = cmd.ExecuteReader()
54
55 If rdr.Read() Then
56 MessageBox.Show("Class Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
57 txtClass.Text = ""
58 txtClass.Focus()
59 If (rdr IsNot Nothing) Then
60 rdr.Close()
61 End If
62 Return
63 End If
64
65 con = New SqlConnection(cs)
66 con.Open()
67
68 Dim cb As String = "insert into Class(ClassName,ClassType) VALUES (@d1,@d2)"
69 cmd = New SqlCommand(cb)
70 cmd.Connection = con
71 cmd.Parameters.AddWithValue("@d1", txtClass.Text)
72 cmd.Parameters.AddWithValue("@d2", cmbClassType.Text)
73 cmd.ExecuteReader()
74 con.Close()
75 LogFunc(lblUser.Text, "added the new Class '" & txtClass.Text & "' having Class type '" & cmbClassType.Text & "'")
76 MessageBox.Show("Successfully saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
77 btnSave.Enabled = False
78 Getdata()
79 Catch ex As Exception
80 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
81 End Try
82 End Sub
83
84 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
85 Try
86 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
87 DeleteRecord()
88 End If
89 Catch ex As Exception
90 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
91 End Try
92 End Sub
93 Private Sub DeleteRecord()
94
95 Try
96
97 Dim RowsAffected As Integer = 0
98 con = New SqlConnection(cs)
99 con.Open()
100 Dim cl As String = "select Classname from Class,Section where Class.Classname=Section.Class and Classname=@d1"
101 cmd = New SqlCommand(cl)
102 cmd.Connection = con
103 cmd.Parameters.AddWithValue("@d1", cmbClassType.Text)
104 rdr = cmd.ExecuteReader()
105 If rdr.Read Then
106 MessageBox.Show("Unable to delete..Already in use in Section Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
107 If Not rdr Is Nothing Then
108 rdr.Close()
109 End If
110 Exit Sub
111 End If
112 con = New SqlConnection(cs)
113 con.Open()
114 Dim cl1 As String = "select Classname from Class,CourseFee where Class.Classname=CourseFee.Class and Classname=@d1"
115 cmd = New SqlCommand(cl1)
116 cmd.Connection = con
117 cmd.Parameters.AddWithValue("@d1", cmbClassType.Text)
118 rdr = cmd.ExecuteReader()
119 If rdr.Read Then
120 MessageBox.Show("Unable to delete..Already in use in Course Fee Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
121 If Not rdr Is Nothing Then
122 rdr.Close()
123 End If
124 Exit Sub
125 End If
126 con = New SqlConnection(cs)
127 con.Open()
128 Dim cl2 As String = "select Classname from Class,Installment_Hostel where Class.Classname=Installment_Hostel.Class and Classname=@d1"
129 cmd = New SqlCommand(cl2)
130 cmd.Connection = con
131 cmd.Parameters.AddWithValue("@d1", cmbClassType.Text)
132 rdr = cmd.ExecuteReader()
133 If rdr.Read Then
134 MessageBox.Show("Unable to delete..Already in use in Hostel Installment Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
135 If Not rdr Is Nothing Then
136 rdr.Close()
137 End If
138 Exit Sub
139 End If
140 con = New SqlConnection(cs)
141 con.Open()
142 Dim cq As String = "delete from Class where Classname=@d1"
143 cmd = New SqlCommand(cq)
144 cmd.Connection = con
145 cmd.Parameters.AddWithValue("@d1", txtClassname.Text)
146 RowsAffected = cmd.ExecuteNonQuery()
147 If RowsAffected > 0 Then
148 LogFunc(lblUser.Text, "deleted the Class '" & txtClass.Text & "' having Class type '" & cmbClassType.Text & "'")
149 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
150 Getdata()
151 Reset()
152 Else
153 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
154 Reset()
155 End If
156 If con.State = ConnectionState.Open Then
157 con.Close()
158
159 End If
160 Catch ex As Exception
161 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
162 End Try
163 End Sub
164
165 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
166 Try
167 If Len(Trim(txtClass.Text)) = 0 Then
168 MessageBox.Show("Please enter class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
169 txtClass.Focus()
170 Exit Sub
171 End If
172 If Len(Trim(cmbClassType.Text)) = 0 Then
173 MessageBox.Show("Please select class type", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
174 cmbClassType.Focus()
175 Exit Sub
176 End If
177 con = New SqlConnection(cs)
178 con.Open()
179 Dim cb As String = "update Class set ClassName=@d1,ClassType=@d2 where ClassName=@d3"
180 cmd = New SqlCommand(cb)
181 cmd.Connection = con
182 cmd.Parameters.AddWithValue("@d1", txtClass.Text)
183 cmd.Parameters.AddWithValue("@d2", cmbClassType.Text)
184 cmd.Parameters.AddWithValue("@d3", txtClassname.Text)
185 cmd.ExecuteReader()
186 con.Close()
187 LogFunc(lblUser.Text, "updated the class '" & txtClass.Text & "' having class type '" & cmbClassType.Text & "'")
188 MessageBox.Show("Successfully updated", "Class Info", MessageBoxButtons.OK, MessageBoxIcon.Information)
189 btnUpdate.Enabled = False
190 Getdata()
191 Catch ex As Exception
192 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
193 End Try
194 End Sub
195 Public Sub Getdata()
196 Try
197 con = New SqlConnection(cs)
198 con.Open()
199 cmd = New SqlCommand("SELECT RTRIM(ClassName), RTRIM(ClassType) from Class order by ClassName", con)
200 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
201 dgw.Rows.Clear()
202 While (rdr.Read() = True)
203 dgw.Rows.Add(rdr(0), rdr(1))
204 End While
205 con.Close()
206 Catch ex As Exception
207 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
208 End Try
209 End Sub
210 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
211 Reset()
212 End Sub
213
214
215 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
216 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
217 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
218 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
219 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
220 End If
221 Dim b As Brush = SystemBrushes.ControlText
222 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
223
224 End Sub
225
226 Private Sub frmCategory_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
227 Getdata()
228 fillCombo()
229 End Sub
230
231 Private Sub dgw_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
232 Try
233 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
234 txtClass.Text = dr.Cells(0).Value.ToString()
235 txtClassname.Text = dr.Cells(0).Value.ToString()
236 cmbClassType.Text = dr.Cells(1).Value.ToString()
237 btnUpdate.Enabled = True
238 btnDelete.Enabled = True
239 btnSave.Enabled = False
240 Catch ex As Exception
241 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
242 End Try
243 End Sub
244
245 Private Sub txtSearchByCategory_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSearchByClass.TextChanged
246 Try
247 con = New SqlConnection(cs)
248 con.Open()
249 cmd = New SqlCommand("SELECT RTRIM(ClassName), RTRIM(ClassType) from Class where ClassName like '" & txtSearchByClass.Text & "%' order by ClassName", con)
250 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
251 dgw.Rows.Clear()
252 While (rdr.Read() = True)
253 dgw.Rows.Add(rdr(0), rdr(1))
254 End While
255 con.Close()
256 Catch ex As Exception
257 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
258 End Try
259 End Sub
260
261 Private Sub txtSearchByClassType_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtSearchByClassType.TextChanged
262 Try
263 con = New SqlConnection(cs)
264 con.Open()
265 cmd = New SqlCommand("SELECT RTRIM(ClassName), RTRIM(ClassType) from Class where ClassType like '" & txtSearchByClassType.Text & "%' order by ClassName", con)
266 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
267 dgw.Rows.Clear()
268 While (rdr.Read() = True)
269 dgw.Rows.Add(rdr(0), rdr(1))
270 End While
271 con.Close()
272 Catch ex As Exception
273 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
274 End Try
275 End Sub
276 End Class